
	**************** 
	** Compile summary indicators
	****************
	
	cap program drop cleancountrynames
	program define cleancountrynames
	
		replace country="Albania" if country=="Albania"
		replace country="Algeria" if country=="Algeria"
		replace country="Angola" if country=="Angola"
		replace country="Azerbaijan" if country=="Azerbaijan"
		replace country="Bahamas, The" if country=="Bahamas"
		replace country="Bolivia" if country=="Bolivia, Plurinational State of"
		replace country="Brazil" if country=="Brazil"
		replace country="Burundi" if country=="Burundi"
		replace country="Cabo Verde" if country=="Cabo Verde"
		replace country="China" if country=="China"
		replace country="Colombia" if country=="Colombia"
		replace country="Congo, Dem. Rep." if country=="Congo, Democratic Republic of the"
		replace country="Congo, Rep." if country=="Congo"
		replace country="Costa Rica" if country=="Costa Rica"
		replace country="Cote d'Ivoire" if country=="Côte d'Ivoire"
		replace country="Czechia" if country=="Czech Republic"
		replace country="Dominican Republic" if country=="Dominican Republic"
		replace country="Egypt, Arab Rep." if country=="Egypt"
		replace country="Gabon" if country=="Gabon"
		replace country="Gambia, The" if country=="The Gambia"
		replace country="Hong Kong SAR, China" if country=="Hong Kong, China"
		replace country="Iran, Islamic Rep." if country=="Iran, Islamic Repbulic of"
		replace country="Iran, Islamic Rep." if country=="Iran, Islamic Republic of"
		replace country="Korea, Rep." if country=="Korea, Republic of"
		replace country="Kyrgyz Republic" if country=="Kyrgyzstan"
		replace country="Lao PDR" if country=="Lao People's Dem. Rep."
		replace country="Lao PDR" if country=="Lao People's Democratic Republic"
		replace country="Lebanon" if country=="Lebanon"
		replace country="North Macedonia" if country=="Macedonia, the former Yugoslav Republic of"
		replace country="Malaysia" if country=="Malaysia"
		replace country="Moldova" if country=="Moldova, Republic of"
		replace country="Nicaragua" if country=="Nicaragua"
		replace country="Panama" if country=="Panama"
		replace country="Philippines" if country=="Philippines"
		replace country="St. Kitts and Nevis" if country=="Saint Kitts and Nevis"
		replace country="St. Lucia" if country=="Saint Lucia"
		replace country="St. Vincent and the Grenadines" if country=="Saint Vincent and the Grenadines"
		replace country="Samoa" if country=="Samoa"
		replace country="Slovak Republic" if country=="Slovakia"
		replace country="Sri Lanka" if country=="Sri Lanka"
		replace country="Eswatini" if country=="Swaziland"
		replace country="Tanzania" if country=="Tanzania, United Republic of"
		replace country="Turkiye" if country=="Turkey"
		replace country="Uruguay" if country=="Uruguay"
		replace country="Vanuatu" if country=="Vanuatu"
		replace country="Venezuela, RB" if country=="Venezuela, Bolivarian Republic of"
		replace country="Vietnam" if country=="Viet Nam"
		replace country="Yemen, Rep." if country=="Yemen"
		
	end

	
	** social protection
	
		insheet using "$dir/rawdata/Social Protection/socialprotection_54615.csv", names clear
			
			ren CountryTerritory region
			
			ren socialprot_expshare_health socexphealth
			ren socialprot_expshare_nohealth socexpnohealth
			
			drop socialprot_expshare_health_year socialprot_expshare_nohealth_yea
			
			qui forvalues x=0/9 { // remove footnotes
				replace country=subinstr(country,"`x'","",.)
			}
			
			foreach var in socexphealth socexpnohealth {
				replace `var'="" if `var'=="…"
				destring `var', replace
			}
			
			cleancountrynames
			
			tempfile datasocialprotect
			save `datasocialprotect', replace
			
	** pensions
	
		insheet using "$dir/rawdata/Pensions/PensionCoverage_54609.csv", names clear
		
			keep CountryTerritory countryterritory total
			
			ren CountryTerritory region
			ren countryterritory country
			ren total pensioncoverage
			
			qui forvalues x=0/9 { // remove footnotes
				replace country=subinstr(country,"`x'","",.)
			}
			
			replace region=strtrim(region)
			replace country=strtrim(country)
			
			drop if missing(region) & missing(country) & missing(pensioncoverage)
			replace pensioncoverage="" if pensioncoverage=="..."
			destring pensioncoverage, replace
			
			cleancountrynames
			
			drop region
	
		tempfile datapensions
		save `datapensions', replace
		
	** UHC service coverage index
		
		insheet using "$dir/rawdata/UHC/data/UHC_INDEX_REPORTED.csv", names clear
		
			keep spatialdimension spatialdimensionvaluecode value timedim
			
			keep if spatialdimension=="COUNTRY"
			drop spatialdimension
			
			ren spatialdimensionvaluecode country_code
			ren value uhcidx
			
			gsort country_code -timedim 
			
			bys country_code: gen index=_n
			
			keep if index==1
			drop index timedim
			
		tempfile datauhc
		save `datauhc', replace
		
	** UHC subindex: infectious diseases
		insheet using "$dir/rawdata/UHC/data/UHC_SCI_INFECT.csv", names clear
		
			keep spatialdimension spatialdimensionvaluecode value timedim
			
			keep if spatialdimension=="COUNTRY"
			drop spatialdimension
			
			ren spatialdimensionvaluecode country_code
			ren value uhcidxinf
			
			gsort country_code -timedim 
			
			bys country_code: gen index=_n
			
			keep if index==1
			drop index timedim
			
		tempfile datauhcinfect
		save `datauhcinfect', replace
		
	** UHC subindex: NCDs
		insheet using "$dir/rawdata/UHC/data/UHC_SCI_NCD.csv", names clear

			keep spatialdimension spatialdimensionvaluecode value timedim
			
			keep if spatialdimension=="COUNTRY"
			drop spatialdimension
			
			ren spatialdimensionvaluecode country_code
			ren value uhcidxncd
			
			gsort country_code -timedim 
			
			bys country_code: gen index=_n
			
			keep if index==1
			drop index timedim
			
		tempfile datauhcncd
		save `datauhcncd', replace
		
	** other metrics
	
		qui foreach metric in population physicians hospbeds oopexp govtexpgdp {
		
			** population
			if "`metric'"=="population" {
				insheet using "$dir/rawdata/Population/API_SP.POP.TOTL_DS2_en_csv_v2_5358404.csv", names clear
			}
			
			** physicians
			if "`metric'"=="physicians" {
				insheet using "$dir/rawdata/Physicians/API_SH.MED.PHYS.ZS_DS2_en_csv_v2_5359574.csv", names clear
			}
		
			** hospital beds
			if "`metric'"=="hospbeds" {
				insheet using "$dir/rawdata/Hospital Beds/API_SH.MED.BEDS.ZS_DS2_en_csv_v2_5363904.csv", names clear
			}
			
			** out of pocket expenditures
			if "`metric'"=="oopexp" {
				insheet using "$dir/rawdata/OOP expenditures/API_SH.XPD.OOPC.CH.ZS_DS2_en_csv_v2_5363985.csv", names clear
			}
				
			** government expenditures as a percent of GDP
			if "`metric'"=="govtexpgdp" {
				insheet using "$dir/rawdata/Gov expenditures pct GDP/API_SH.XPD.GHED.GD.ZS_DS2_en_csv_v2_5363980.csv", names clear
			}
			
			drop if _n==1
			ren datasource country
			ren worlddevelopmentindicators country_code
			drop v3 v4
			foreach var of varlist v5-v67 {
				di "`var'"
				sum `var' if _n==1
				ren `var' `metric'`r(mean)'
			}
			drop `metric'
			drop if _n==1
			
			reshape long `metric', i(country country_code) j(year)
			drop if missing(`metric')
			bys country_code: gen count=_N
			sort country_code year
			bys country_code: gen index=_n
			keep if count==index
			drop count index
			
			ren year `metric'_year
			
			tempfile data`metric'
			save `data`metric'', replace
			
		}
	
	** income groups and regional classifications
	
		import excel using "$dir/rawdata/Income Groups/CLASS.xlsx", firstrow clear
			drop Lendingcategory OtherEMUorHIPC
			
			ren Economy country
			ren Code country_code
			ren Region region
			ren Incomegroup incomegroup
		
			drop if missing(region)
			
			replace country=strtrim(country)
			
			replace country="Cote d'Ivoire" if strpos(country,"Ivoire")==11 // for some reason cannot get to work otherwise
			replace country="Curacao" if country=="Curaçao"
			replace country="Czechia" if country=="Czech Republic"
			replace country="Turkiye" if country=="Türkiye"
			replace country="Sao Tome and Principe" if country=="São Tomé and Príncipe"
			
			replace incomegroup="Upper middle income" if country=="Venezuela, RB"
			replace region="Sub-Saharan Africa" if country=="Djibouti" // to align with UNDP definition
			replace region="Middle East & North Africa" if country=="Sudan" // to align with UNDP definition
			
			tempfile incomegroups
			save `incomegroups', replace
			
	** merge
	
		clear
		use `datapopulation', clear
		merge 1:1 country using `dataphysicians', nogen
		merge 1:1 country using `datahospbeds', nogen
		merge 1:1 country using `dataoopexp', nogen
		merge 1:1 country using `datagovtexpgdp', nogen
		merge 1:1 country using `datapensions', gen(merge_pensions)
			drop if merge_pensions==2 // Jersey, Occupied Palestinian Territory
			drop merge_pensions
		merge 1:1 country using `datasocialprotect', gen(merge_socialprotect)
			drop if merge_socialprotect==2 // Taiwan
			drop merge_socialprotect
		merge 1:1 country using `incomegroups', gen(temp) // nogen
			sort country
			*br temp country if temp!=3 // mostly non-countries (aside from Taiwan)
			replace country=country+" (from WDI)" if temp!=3
			drop temp
		merge 1:1 country_code using `datauhc', nogen keep(master matched)		
		merge 1:1 country_code using `datauhcinfect', nogen keep(master matched)		
		merge 1:1 country_code using `datauhcncd', nogen keep(master matched)
			
	** population-weighted averages by income group and region
	
		gen incomegroup_det=incomegroup
	
		replace incomegroup="HICs" if incomegroup=="High income"
		replace incomegroup="LMICs_nonSSA" if incomegroup=="Low income" | incomegroup=="Lower middle income" | incomegroup=="Upper middle income"
		replace incomegroup="" if region=="Sub-Saharan Africa"
				
		bys incomegroup: gen count_incomegroup=_N
		bys incomegroup_det: gen count_incomegroup_det=_N
		bys region: gen count_region=_N
		
		foreach var in population physicians hospbeds oopexp govtexpgdp pensioncoverage socexphealth socexpnohealth uhcidx uhcidxinf uhcidxncd {
			
			** nonmissing by income group
				bys incomegroup: egen `var'_nm_incgroup=count(`var')
				replace `var'_nm_incgroup=`var'_nm_incgroup/count_incomegroup
				
			** income group average, weighting by population
				bys incomegroup: egen `var'_incgroup=wtmean(`var'), weight(population)
				
				** nonmissing by more detailed income group
					bys incomegroup_det: egen `var'_nm_incgroup_det=count(`var')
					replace `var'_nm_incgroup_det=`var'_nm_incgroup_det/count_incomegroup_det
				
				** income group average, weighting by population
					bys incomegroup_det: egen `var'_incgroup_det=wtmean(`var'), weight(population)
			
			** nonmissing by region
				bys region: egen `var'_nm_region=count(`var')
				replace `var'_nm_region=`var'_nm_region/count_region
				
			** regional averages, weighting by population
				bys region: egen `var'_region=wtmean(`var'), weight(population)
				
		}
	
	** regional and income-group averages
	
		preserve
			keep if region=="Sub-Saharan Africa"
			keep region *region
			duplicates drop
			ren region country
			gen country_code="SSA"
			
			ren *_region *
			
			tempfile ssa
			save `ssa', replace
		restore
		
		preserve
			keep if incomegroup=="HICs"
			keep incomegroup *incgroup
			duplicates drop
			ren incomegroup country
			gen country_code="HICs"
			
			ren *_incgroup *
			
			tempfile hics
			save `hics', replace
		restore
		
		preserve
			keep if incomegroup=="LMICs_nonSSA"
			keep incomegroup *incgroup
			duplicates drop
			ren incomegroup country
			gen country_code="LMICs_nonSSA"
			
			ren *_incgroup *
			
			tempfile lmics
			save `lmics', replace
		restore
		
		preserve
			keep if incomegroup_det=="Low income"
			keep incomegroup_det *incgroup_det
			duplicates drop
			ren incomegroup_det country
			gen country_code="Low income"
			
			ren *_incgroup_det *
			
			tempfile lowincomes
			save `lowincomes', replace
		restore
		
		preserve
			keep if incomegroup_det=="Lower middle income"
			keep incomegroup_det *incgroup_det
			duplicates drop
			ren incomegroup_det country
			gen country_code="Lower middle income"
			
			ren *_incgroup_det *
			
			tempfile lowmidincomes
			save `lowmidincomes', replace
		restore
		
		preserve
			keep if incomegroup_det=="Upper middle income"
			keep incomegroup_det *incgroup_det
			duplicates drop
			ren incomegroup_det country
			gen country_code="Upper middle income"
			
			ren *_incgroup_det *
			
			tempfile uppermidincomes
			save `uppermidincomes', replace
		restore
		
		append using `ssa'
		append using `hics'
		append using `lmics'
		append using `lowincomes'
		append using `lowmidincomes'
		append using `uppermidincomes'
		
		gen keep=1 if country=="Uganda"
		replace keep=1 if country=="Nigeria"
		replace keep=1 if country=="Tanzania"
		replace keep=1 if country=="South Africa"
		replace keep=1 if country=="Ghana"
		replace keep=1 if country=="Malawi"
		replace keep=1 if country=="Ethiopia"
		replace keep=1 if country=="Niger"
		
		gen type="Country" if country=="Uganda"
		replace type="Country" if country=="Nigeria"
		replace type="Country" if country=="Tanzania"
		replace type="Country" if country=="South Africa"
		replace type="Country" if country=="Ghana"
		replace type="Country" if country=="Malawi"
		replace type="Country" if country=="Ethiopia"
		replace type="Country" if country=="Niger"
		
		replace keep=1 if country=="LMICs_nonSSA"
		replace keep=1 if country=="HICs"
		replace keep=1 if country=="Sub-Saharan Africa"
		
		replace keep=1 if country=="Low income"
		replace keep=1 if country=="Lower middle income"
		replace keep=1 if country=="Upper middle income"
		
		replace type="Region or income group (for figure)" if country=="LMICs_nonSSA"
		replace type="Region or income group (for figure)" if country=="HICs"
		replace type="Region or income group (for figure)" if country=="Sub-Saharan Africa"
		
		replace type="Region or income group (to double check)" if country=="Low income"
		replace type="Region or income group (to double check)" if country=="Lower middle income"
		replace type="Region or income group (to double check)" if country=="Upper middle income"
		
		replace keep=1 if country=="Sub-Saharan Africa (from WDI)"
		replace keep=1 if country=="Sub-Saharan Africa (excluding high income) (from WDI)"
		replace keep=1 if country=="Low & middle income (from WDI)"
		replace keep=1 if country=="Low income (from WDI)"
		replace keep=1 if country=="Middle income (from WDI)"
		replace keep=1 if country=="High income (from WDI)"
		
		replace type="Region or income group (to double check)" if country=="Sub-Saharan Africa (from WDI)"
		replace type="Region or income group (to double check)" if country=="Sub-Saharan Africa (excluding high income) (from WDI)"
		replace type="Region or income group (to double check)" if country=="Low & middle income (from WDI)"
		replace type="Region or income group (to double check)" if country=="Low income (from WDI)"
		replace type="Region or income group (to double check)" if country=="Middle income (from WDI)"
		replace type="Region or income group (to double check)" if country=="High income (from WDI)"
				
		keep if keep==1
				
		keep country country_code type population* physicians* hospbeds* oopexp* govtexpgdp* pensioncoverage* socexphealth* socexpnohealth* uhcidx* uhcidxinf* uhcidxncd* 
		drop *_region *_incgroup *_incgroup_det		
		
	** LMIC excluding SSA (testing alternate way)
	
		* first get population shares
			sum population if country=="Low & middle income (from WDI)"
			local pop_LMIC=`r(mean)'
			sum population if country=="Sub-Saharan Africa (excluding high income) (from WDI)"
			local pop_SSA=`r(mean)'
			
			local popshare_SSA_LMIC=`pop_SSA'/`pop_LMIC'
			local popshare_nonSSA_LMIC=1-`popshare_SSA_LMIC'
			
		* then populate non-SSA figures
			insobs 1
			replace country="Low & middle income (excluding SSA)" if missing(country)
			
			foreach metric in population physicians hospbeds oopexp govtexpgdp pensioncoverage uhcidx uhcidxinf uhcidxncd {

				sum `metric' if country=="Low & middle income (from WDI)"
				if `r(N)'>0 local LMIC_avg=`r(mean)'
				sum `metric' if country=="Sub-Saharan Africa (excluding high income) (from WDI)"
				if `r(N)'>0 local SSA_avg=`r(mean)'
				
				if `r(N)'>0 replace `metric'=(`LMIC_avg'-`popshare_SSA_LMIC'*`SSA_avg')/`popshare_nonSSA_LMIC' if country=="Low & middle income (excluding SSA)"

			}
			
		drop if country=="Low & middle income (excluding SSA)"
		
	** Merge in life expectancies
	
		merge 1:1 country using "$dir/data/lifeexpectancy2019.dta", nogen assert(master matched)
		merge 1:1 country using "$dir/data/lifeexpectancy2000.dta", nogen assert(master matched)
		
	** Save
		
		order country country_code type
		order *_year *_nm, after(lifeexpsixtytotal)
		sort type country
		
		save "$dir/data/summaryindicators.dta", replace
	